import datetime as dt
from dateutil.parser import parse
import os
import sys

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from typing import List
from src.tasks.enterprise.EntEnterprise import EntEnterprise
from src.utils import CommonUtils, BatchUtils


class SyncAbsTables:
    def __init__(self, logger, localDb, absDb):
        self.logger = logger
        self.localDb = localDb
        self.absDb = absDb

    def __deleteAbsTag__(self):
        sql = """delete from abs_deals_tag """
        return self.localDb.ExecNoneQuery(sql)

    def __queryTagInfo__(self):
        sql = """
            SELECT * from abs_deals_tag
                  """
        return self.absDb.Query(sql)

    def __updateTagLocal__(self, updateDatas):
        sql = """
            REPLACE INTO `abs_deals_tag` (`ID`, `ModifyTime`, `IsDeleted`, `DealId`, `Tag`, `TagType`) VALUES 
            (%s , %s, %s, %s, %s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)

    # # abs_deals_tag
    def __updAbsTagInfo__(self):
        flag = 0
        self.__deleteAbsTag__()

        dataInfo = self.__queryTagInfo__()

        updateDatas = []
        for r in dataInfo:
            updateDatas.append(list(r.values()))
        BatchUtils.batchInsert(self.__updateTagLocal__, updateDatas)


    def __deletedRatingConfig__(self):
        sql = """delete from org_rating_config """
        return self.localDb.ExecNoneQuery(sql)

    def __queryRatingConfigInfo__(self):
        sql = """
            SELECT * from org_rating_config
                  """
        return self.absDb.Query(sql)

    def __updatedRatingConfigLocal__(self, updateDatas):
        sql = """
            REPLACE INTO `org_rating_config` (`ID`, `rating`, `sort`) VALUES 
            (%s , %s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)

    # # org_rating_config
    def __updRatingConfigInfo__(self):
        flag = 0
        self.__deletedRatingConfig__()

        dataInfo = self.__queryRatingConfigInfo__()

        updateDatas = []
        for r in dataInfo:
            updateDatas.append(list(r.values()))
        BatchUtils.batchInsert(self.__updatedRatingConfigLocal__, updateDatas)


    def __deleteAbsBondsRating__(self):
        sql = """delete from abs_bonds_rating where sync_time <= DATE_SUB(NOW(),INTERVAL 30 MINUTE) """
        return self.localDb.ExecNoneQuery(sql)

    def __queryAbsBondsRatingByDate__(self, start, end):
            sql = """
            SELECT * from abs_bonds_rating order by id limit %s, %s
            """
            return self.absDb.Query(sql, (start, end))

    def __updateAbsBondsRatingInfo__(self, updateDatas):
        sql = """
        REPLACE INTO `abs_bonds_rating` (`id`, `MaxOriginalRating`, `BondId`, `create_time`, `MaxOriginalRatingAgency`) VALUES 
        (%s, %s, %s, %s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)

    # # abs_bonds_rating
    def __updAbsBondsRating__(self):
        flag = 0
        count = 0
        # 加载需要同步的数据
        dataInfo = self.__queryAbsBondsRatingByDate__(count,50000)
        count = count + 50000
        while flag == 0:
            updateDatas = []
            for r in dataInfo:
                updateDatas.append(list(r.values()))
            if len(dataInfo) < 50000:
                flag = 1
            BatchUtils.batchInsert(self.__updateAbsBondsRatingInfo__, updateDatas)
            dataInfo = self.__queryAbsBondsRatingByDate__(count,50000)
            count = count + 50000
        self.__deleteAbsBondsRating__()


    # # abs_assets_distribution
    def __updAbsAssetsDistribution__(self):
        flag = 0
        count = 0
        # 加载需要同步的数据
        dataInfo = self.__queryAssetsDistributionByDate__(count,50000)
        count = count + 50000
        while flag == 0:
            updateDatas = []
            for r in dataInfo:
                updateDatas.append(list(r.values()))
            if len(dataInfo) < 50000:
                flag = 1
            BatchUtils.batchInsert(self.__updateAssetsDistributionInfo__, updateDatas)
            dataInfo = self.__queryAssetsDistributionByDate__(count,50000)
            count = count + 50000
        self.__deleteAssetsDistribution__()


    def __deleteAssetsDistribution__(self):
        sql = """delete from abs_assets_distribution where sync_time <= DATE_SUB(NOW(),INTERVAL 30 MINUTE) """
        return self.localDb.ExecNoneQuery(sql)

    def __queryAssetsDistributionByDate__(self, start, end):
            sql = """
            SELECT * from abs_assets_distribution order by id limit %s, %s
            """
            return self.absDb.Query(sql, (start, end))

    def __updateAssetsDistributionInfo__(self, updateDatas):
        sql = """
        REPLACE INTO `abs_assets_distribution` (`ID`, `ModifyTime`, `IsDeleted`, `DealId`, `PaymentDate`, `Name`, `SubName`, `Section`, `AssetCount`, `CountPercent`, `Balance`, `BalancePercent`) 
        VALUES 
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s ,%s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)


    # # abs_assets_collection_cashflows
    def __updAbsAssetsCollectionCashflows__(self):
        flag = 0
        count = 0
        # 加载需要同步的数据
        dataInfo = self.__queryAssetsCollectionCashflowsByDate__(count,50000)
        count = count + 50000
        while flag == 0:
            updateDatas = []
            for r in dataInfo:
                updateDatas.append(list(r.values()))
            if len(dataInfo) < 50000:
                flag = 1
            BatchUtils.batchInsert(self.__updateAssetsCollectionCashflows__, updateDatas)
            dataInfo = self.__queryAssetsCollectionCashflowsByDate__(count,50000)
            count = count + 50000
        self.__deleteAssetsCollectionCashflows__()


    def __deleteAssetsCollectionCashflows__(self):
        sql = """delete from abs_assets_collection_cashflows where sync_time <= DATE_SUB(NOW(),INTERVAL 30 MINUTE) """
        return self.localDb.ExecNoneQuery(sql)

    def __queryAssetsCollectionCashflowsByDate__(self, start, end):
            sql = """
            SELECT * from abs_assets_collection_cashflows order by id limit %s, %s
            """
            return self.absDb.Query(sql, (start, end))

    def __updateAssetsCollectionCashflows__(self, updateDatas):
        sql = """
        REPLACE INTO `abs_assets_collection_cashflows` (`ID`, `ModifyTime`, `IsDeleted`, `DealId`, `PaymentDate`, `CollectionDate`, `Balance`, `ReinvestmentAmount`, `DefaultAmount`, `PrincipalAmount`, `PrincipalSchedule`, `PrincipalPrepayment`, `PrincipalDefaultRecovery`, `PrincipalDelinqRecovery`, `InterestAmount`, `InterestSchedule`, `InterestPrepayment`, `InterestDefaultRecovery`, `InterestDelinqRecovery`, `PrincipalDisposal`, `PrincipalCleanupRepurchase`, `PrincipalRedemption`, `PrincipalOther`, `InterestDisposal`, `InterestCleanupRepurchase`, `InterestRedemption`, `InterestOther`, `PrincipalPreviousRetained`, `InterestPreviousRetained`, `InvestIncomePrincipal`, `InvestIncomeInterest`) 
        VALUES 
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s ,%s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s ,%s, %s, %s, %s, %s, %s, %s, %s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)





    # # abs_assets_promised_cashflows
    def __updPromisedCashFlows__(self):
        # self.__getDateDiff__('2025-02-25')
        # a = 42159<<14;
        # print(a)
        # b = (a|1)<<12;
        flag = 0
        count = 0
        # 加载需要同步的数据
        timeData = self.__querySyncTimeLine__()
        if timeData is None:
            dataInfo = self.__queryCashFlows__(count,50000)
            count = count + 50000
            while flag == 0:
                updateDatas = []
                for r in dataInfo:
                    sortId = self.__getSortId__(r)
                    updateDatas.append((
                        r["ID"],
                        r["ModifyTime"],
                        r["IsDeleted"],
                        r["DealId"],
                        r["PaymentDate"],
                        r["BeginDate"],
                        r["EndDate"],
                        r["BeginningBalance"],
                        r["Balance"],
                        r["Principal"],
                        r["Interest"],
                        r["IsCombined"],
                        r["IsNotional"],
                        r["Scenario"],
                        sortId
                    ))
                    # updateDatas.append(list(r.values()))
                if len(dataInfo) < 50000:
                    flag = 1
                BatchUtils.batchInsert(self.__updateCashFlowsDataInfo__, updateDatas)
                dataInfo = self.__queryCashFlows__(count,50000)
                count = count + 50000
        else:
            dataInfo = self.__queryCashFlowsByDate__(timeData["ModifyTime"], count, 50000)
            count = count + 50000
            while flag == 0:
                updateDatas = []
                for r in dataInfo:
                    sortId = self.__getSortId__(r)
                    updateDatas.append((
                        r["ID"],
                        r["ModifyTime"],
                        r["IsDeleted"],
                        r["DealId"],
                        r["PaymentDate"],
                        r["BeginDate"],
                        r["EndDate"],
                        r["BeginningBalance"],
                        r["Balance"],
                        r["Principal"],
                        r["Interest"],
                        r["IsCombined"],
                        r["IsNotional"],
                        r["Scenario"],
                        sortId
                    ))
                if len(dataInfo) < 50000:
                    flag = 1
                BatchUtils.batchInsert(self.__updateCashFlowsDataInfo__, updateDatas)
                dataInfo = self.__queryCashFlowsByDate__( timeData["ModifyTime"] ,count,50000)
                count = count + 50000

    def __getSortId__(self, data):
        sortId = data["DealId"]
        day1 = "20"+sortId[2:8]
        data1 = sortId[8:12]
        dt = parse(day1)
        day1 = dt.strftime("%Y-%m-%d")
        countData = self.__getDateDiff__(day1)
        a = countData<<14;
        # print(countData)
        data1 = int(data1)
        b = (a|data1)<<16;
        # print(b)
        paymentDate = data["PaymentDate"]
        if paymentDate is None:
            paymentDate = 0
        else:
            paymentDate = paymentDate.strftime("%Y-%m-%d")
            # paymentDate = paymentDate[0:10]
            paymentDate = self.__getDateDiff__(paymentDate)
        c = (b |paymentDate) << 16;

        EndDate = data["EndDate"]
        if EndDate is None:
            EndDate = 0
        else:
            EndDate = EndDate.strftime("%Y-%m-%d")
            # EndDate = EndDate[0:10]
            EndDate = self.__getDateDiff__(EndDate)
        d = (c |EndDate)
        return d

    def __getDateDiff__(self, d2):
        d1 = '1900-01-01'
        date1 = dt.datetime.strptime(d1, "%Y-%m-%d").date()  ##datetime.date(2018, 1, 6)
        date2 = dt.datetime.strptime(d2, "%Y-%m-%d").date()  ##datetime.date(2018, 1, 9)
        Days = (date2 - date1).days + 2
        return Days

    def __queryCashFlows__(self, start, end):
            sql = """
                SELECT *
                FROM abs_assets_promised_cashflows order by id LIMIT %s , %s 
            """
            return self.absDb.Query(sql, (start, end))


    def __queryCashFlowsByDate__(self, time , start, end ):
            sql = """
                SELECT *
                FROM abs_assets_promised_cashflows where ModifyTime >= %s order by id LIMIT %s , %s 
            """
            return self.absDb.Query(sql, (time, start, end))

    def __updateCashFlowsDataInfo__(self, updateDatas):
        sql = """
        REPLACE INTO `abs_assets_promised_cashflows_v2` (`ID`, `ModifyTime`, `IsDeleted`, `DealId`, `PaymentDate`, `BeginDate`, `EndDate`, `BeginningBalance`, `Balance`, `Principal`, `Interest`, `IsCombined`, `IsNotional`, `Scenario`,`sortId`) VALUES 
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s ,%s, %s,%s, %s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)

    def __querySyncTimeLine__(self):
        sql = """SELECT DATE_FORMAT(ModifyTime, '%Y-%m-%d %H:%i:%s') AS ModifyTime from abs_assets_promised_cashflows_v2 
                 order by ModifyTime desc LIMIT 1 """
        return self.localDb.QueryOne(sql)




    # # abs_bonds_trade
    def __updAbsBondsTrade__(self):
        flag = 0
        count = 0
        # 加载需要同步的数据
        dataInfo = self.__queryAbsBondsTradeByDate__(count,50000)
        count = count + 50000
        while flag == 0:
            updateDatas = []
            for r in dataInfo:
                updateDatas.append(list(r.values()))
            if len(dataInfo) < 50000:
                flag = 1
            BatchUtils.batchInsert(self.__updateAbsBondsTradeByDateInfo__, updateDatas)
            dataInfo = self.__queryAbsBondsTradeByDate__(count,50000)
            count = count + 50000
        self.__deleteAbsBondsTradeByDate__()


    def __deleteAbsBondsTradeByDate__(self):
        sql = """delete from abs_bonds_trade where sync_time <= DATE_SUB(NOW(),INTERVAL 30 MINUTE) """
        return self.localDb.ExecNoneQuery(sql)

    def __queryAbsBondsTradeByDate__(self, start, end):
            sql = """
            SELECT * from abs_bonds_trade order by id limit %s, %s
            """
            return self.absDb.Query(sql, (start, end))

    def __updateAbsBondsTradeByDateInfo__(self, updateDatas):
        sql = """
        REPLACE INTO `abs_bonds_trade` (`ID`, `ModifyTime`, `IsDeleted`, `DealId`, `BondId`, `BondCode`, `BondName`, `Date`, `Time`, `YesterdayReferPrice`, `Open`, `Highest`, `Lowest`, `Newest`, `WeightedAverage`, `Volumn`, `Amount`, `Count`, `YieldOfHighest`, `YieldOfLowest`, `YieldOfNewest`)  
        VALUES 
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s ,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)


    # # abs_cross_sectional_datas
    def __updAbsCrossSectionalDatas__(self):
        flag = 0
        count = 0
        # 加载需要同步的数据
        timeData = self.__queryAbsCrossSectionalDataTimeLine__()
        if timeData is None:
            # 加载需要同步的数据
            dataInfo = self.__queryAbsCrossSectionalDatas__(count,50000)
            count = count + 50000
            while flag == 0:
                updateDatas = []
                for r in dataInfo:
                    updateDatas.append(list(r.values()))
                if len(dataInfo) < 50000:
                    flag = 1
                BatchUtils.batchInsert(self.__updateAbsCrossSectionalDatasInfo__, updateDatas)
                dataInfo = self.__queryAbsCrossSectionalDatas__(count,50000)
                count = count + 50000
        else:
            # 加载需要同步的数据
            dataInfo = self.__queryAbsCrossSectionalDatasByDate__(timeData['ModifyTime'] , count,50000)
            count = count + 50000
            while flag == 0:
                updateDatas = []
                for r in dataInfo:
                    updateDatas.append(list(r.values()))
                if len(dataInfo) < 50000:
                    flag = 1
                BatchUtils.batchInsert(self.__updateAbsCrossSectionalDatasInfo__, updateDatas)
                dataInfo = self.__queryAbsCrossSectionalDatasByDate__(timeData['ModifyTime'],count,50000)
                count = count + 50000

    def __queryAbsCrossSectionalDataTimeLine__(self):
        sql = """SELECT DATE_FORMAT(ModifyTime, '%Y-%m-%d') AS ModifyTime from abs_cross_sectional_datas 
                 order by ModifyTime desc LIMIT 1 """
        return self.localDb.QueryOne(sql)

    def __queryAbsCrossSectionalDatas__(self, start, end):
            sql = """
            SELECT * from  abs_cross_sectional_datas order by id limit %s, %s
            """
            return self.absDb.Query(sql, (start, end))

    def __queryAbsCrossSectionalDatasByDate__(self, date, start, end):
            sql = """
            SELECT * from  abs_cross_sectional_datas where ModifyTime >= %s order by id limit %s, %s
            """
            return self.absDb.Query(sql, (date ,start, end))

    def __updateAbsCrossSectionalDatasInfo__(self, updateDatas):
        sql = """
        REPLACE INTO `abs_cross_sectional_datas` (`ID`, `ModifyTime`, `IsDeleted`, `Date`, `DealId`, `BondId`, `Balance`, `MaturityDate`, `PaymentDate`, `Notes`)
         VALUES 
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s );
        """
        return self.localDb.ExecMany(sql, updateDatas)

    # # abs_bonds_cashflows
    def __updAbsBondsCashFlows__(self):
        flag = 0
        count = 0
        # 加载需要同步的数据
        dataInfo = self.__queryAbsBondsCashFlowsByDate__(count,50000)
        count = count + 50000
        while flag == 0:
            updateDatas = []
            for r in dataInfo:
                updateDatas.append(list(r.values()))
            if len(dataInfo) < 50000:
                flag = 1
            BatchUtils.batchInsert(self.__updateAbsCashFlowsByDateInfo__, updateDatas)
            dataInfo = self.__queryAbsBondsCashFlowsByDate__(count,50000)
            count = count + 50000
        self.__deleteAbsBondsCashFlows__()


    def __deleteAbsBondsCashFlows__(self):
        sql = """delete from abs_bonds_cashflows where sync_time <= DATE_SUB(NOW(),INTERVAL 30 MINUTE) """
        return self.localDb.ExecNoneQuery(sql)

    def __queryAbsBondsCashFlowsByDate__(self, start, end):
            sql = """
            SELECT * from abs_bonds_cashflows order by id limit %s, %s
            """
            return self.absDb.Query(sql, (start, end))

    def __updateAbsCashFlowsByDateInfo__(self, updateDatas):
        sql = """
        REPLACE INTO `abs_bonds_cashflows` (`ID`, `ModifyTime`, `IsDeleted`, `DealId`, `BondId`, `PaymentDate`, `ReportDate`, `AccrualStartDate`, `AccrualEndDate`, `Coupon`, `BeginningBalance`, `Balance`, `Interest`, `Principal`, `PrincipalPaidPercentage`, `InterestPaidPercentage`, `BalancePercentage`) 
        VALUES 
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s ,%s, %s, %s, %s, %s, %s, %s);
        """
        return self.localDb.ExecMany(sql, updateDatas)

